﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Grishko.DataTier.MetaInfo;
using Grishko.DataTier.SqlSentence;
using Grishko.SerilizeTools;
using System.Reflection;
using System.Web.UI.WebControls;

namespace Grishko.DataTier.SqlScriptor
{
    public class Node
    {
        public object Parent{get;set;}
        public object Child{get;set;}
        public object Value;
    }

    /// <summary>
    /// вспомогательный класс движка SqlScript.    
    /// Сформировать скрипт - позволяющий сформировать пространство поиска для выражение.
    /// пройтись по графу указаного типа и выявить все используемые TagName в запросе.
    /// Сосотавить скрипт соответ - BuildSearchArea_1
    /// </summary>
    public class ScriprSearchAreaEngine
    {
        struct FieldInfoItem
        {
            public string TagName { get; set; }
            public string PropertyID { get; set; }
        }


        struct LinkInfoItem
        {
            public string LinkPropertyName { get; set; }
            public string PropertyCaption { get; set; }
        }

        private SelectCommand selectCommand = null;
        public ScriprSearchAreaEngine(SelectCommand cmd)
        {
            selectCommand = cmd;
        }

        public string GetScript()
        {
            StringBuilder sb = new StringBuilder();
            // сформировать коллекцию неродных - прилинкованых свойств объекта                        
            List<LinkFieldAttribute> linkCollection = SrchInfo.GetLinkFieldAttributes(selectCommand.SearchAreaTemplateType);

            //сформировать скрипт выбирающий записи с TagName - учавствующими в запросе
            sb.Append(GetTagRestrictionForSerachArea(linkCollection));

            //скрипт - информация по прилинкованным объектам 
            sb.Append(GetTagLinkScript(linkCollection));

            // сформировать скрипт - запси IsObject=1 по текущему запросу
            sb.Append(GetObjectTagRecords());                        

            //сформировать индексы
            sb.Append(GetIndexScript());

            return sb.ToString();


            
        }

        private string GetIndexScript()
        {
            string result = "CREATE NONCLUSTERED INDEX [idx_GetSideObjectPropertyStorage] ON [dbo].[#SerachAreaTable] " + Environment.NewLine +
                            "([ObjectID] ASC) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] " + Environment.NewLine +

                            "CREATE NONCLUSTERED INDEX [idx_GetSideObjectPropertyStorage_PropertyValue_1] ON [dbo].[#SerachAreaTable] " + Environment.NewLine +
                             "(	[PropertyValue] ASC) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] " + Environment.NewLine +

                            " CREATE NONCLUSTERED INDEX [idx_GetSideObjectPropertyStorage_TagName_1] ON [dbo].[#SerachAreaTable] (	[TagName] ASC )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]" + Environment.NewLine +

                            "CREATE NONCLUSTERED INDEX [idx_GetSideObjectPropertyStorage_TxtPropertyValue_1] ON [dbo].[#SerachAreaTable] " + Environment.NewLine +
                            "([TxtPropertyValue] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]" + Environment.NewLine;
                          
            return result;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        private string GetObjectTagRecords()
        {     
            StringBuilder sb= new StringBuilder ();
            foreach (Word item in selectCommand .Params )
            {
                if(item.Level >1) // запрос к ветке ?
                    sb.Append (GetObjectTagRecordScript(item));
                
            }
        return sb.ToString ();
        }

        /// <summary>
        /// вернуть скрипт генерирующий строчки для заданного параметра
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        private string GetObjectTagRecordScript(Word obj)
        {
            //SqlScriptEngine.ScriptConst_SerachAreaTable
                string Root_format = "INSERT INTO {0} SELECT Parent.*  FROM " + Environment.NewLine +
                                      "vSrchObjectPropertyStorage Parent " + Environment.NewLine +
                                      "JOIN " + Environment.NewLine +
                                      "vSrchObjectPropertyStorage Child " + Environment.NewLine +
                                      "ON Parent.PropertyValue=Child.ObjectID and Parent.IsObject=1 " + Environment.NewLine +
                                      "WHERE  " + Environment.NewLine +
                                GetParamWhere("Child",obj);

                string Node_format = "INSERT INTO {0} SELECT Parent.* FROM " + Environment.NewLine +
                                    "vSrchObjectPropertyStorage Parent " + Environment.NewLine +
                                    "JOIN " + Environment.NewLine +
                                    "{0} Child " + Environment.NewLine +
                                    "ON Parent.PropertyValue=Child.ObjectID and Parent.IsObject=1 AND Parent.TagName='{2}'" + Environment.NewLine;                                    

                    
            StringBuilder sb = new StringBuilder();
            string tagPath = obj.TagPatch;// берем дорожку тегов
            //Формируем массив используемых тегов  - для каждого элемента - формируем скрипт
            string[] arr = tagPath.Split(".".ToCharArray());
            
            int parentIndex = 0;
            string[] paramList = { SqlScriptEngine.ScriptConst_SerachAreaTable, "", "" };
            string script = string.Empty;
            for (int childIndex = arr.Length-1; childIndex >0; childIndex --)
            {
                parentIndex = childIndex - 1;
                paramList[1]=arr[childIndex];
                paramList[2]=arr[(parentIndex>0?parentIndex:0)];
                script = string.Format((childIndex == arr.Length-1? Root_format:Node_format ), paramList);
                sb.Append(script);
            }


            return sb.ToString();
            

        }        
        
        /// <summary>
        ///  Сформировать скрипт линкующий к основной выборке поисковые поля объектов указанных  атрибутом  LinkFieldAttribute
        /// </summary>
        /// <param name="linkCollection"></param>
        /// <returns></returns>
        private string GetTagLinkScript(List<LinkFieldAttribute> linkCollection)
        {
            StringBuilder sb = new StringBuilder();
            //string format = "INSERT INTO {0} " + Environment.NewLine +
            //              "SELECT " + Environment.NewLine +
            //              "R.AgencyID, " + Environment.NewLine +
            //              "R.ObjectID, " + Environment.NewLine +
            //              "L.TagName, " + Environment.NewLine +
            //              "'{1}', " + Environment.NewLine +
            //              "R.ObjectID as  PropertyValue, " + Environment.NewLine +
            //              "'Object', " + Environment.NewLine +
            //              "'{2}', " + Environment.NewLine +
            //              "L.ObjectID, " + Environment.NewLine +
            //              "1, " + Environment.NewLine +
            //              "0 " + Environment.NewLine +
            //              "FROM " + Environment.NewLine +
            //              "{0} R " + Environment.NewLine +
            //              "JOIN vSrchObjectPropertyStorage L  on L.PropertyValue=R.ObjectID " + Environment.NewLine +
            //              "WHERE L.PropertyName='{1}' AND NOT R.PropertyName='ID'";


            string format = "INSERT INTO {0} " + Environment.NewLine +
                            "SELECT " + Environment.NewLine +
                            "SOPS.AgencyID, " + Environment.NewLine +
                            "SOPS.PropertyValue, " + Environment.NewLine +
                            "SOPS.TagName, " + Environment.NewLine +
                            "'{3}', " + Environment.NewLine +
                            "SOPS.ObjectID as  PropertyValue, " + Environment.NewLine +
                            "'Object', " + Environment.NewLine +
                            "'{2}', " + Environment.NewLine +
                            "SOPS.ObjectID , " + Environment.NewLine +
                            "1, " + Environment.NewLine +
                            "0 " + Environment.NewLine +
                             "FROM " + Environment.NewLine +
                            "#SerachAreaTable SAT " + Environment.NewLine +
                            "JOIN vSrchObjectPropertyStorage  SOPS on SAT.ObjectID =SOPS.ObjectID and SOPS.PropertyName='{1}' " + Environment.NewLine;

            
            foreach (LinkFieldAttribute item in linkCollection )
            {
                string[] arr = { SqlScriptEngine.ScriptConst_SerachAreaTable, item.LinkField, item.TxtPropertyName,item .PropertyField};
                sb.Append(string.Format(format, arr));
            }
            
            sb.Append (" INSERT INTO #SerachAreaTable SELECT SOP.* FROM vSrchObjectPropertyStorage SOP join #SerachAreaTable SAT on SAT.ObjectID=SOP.objectID and SAT.PropertyType='Object'");

            return sb.ToString();
        }

        /// <summary>
        /// Вернуть скрипт формирующий набор ограничений соответсвующий запросу
        /// Если selectCommand.Params .Count ==0 либо  selectCommand.Params  есть условие SrchItemInfo.PropertyControlType.OnlyValueSrch  
        /// вернуть скрипт с перечнем всех tagname соотв. типу.
        /// иначе 
        /// - формировать на основе selectCommand.Params
        /// </summary>
        /// <returns></returns>
        private string GetTagRestrictionForSerachArea(List<LinkFieldAttribute> linkCollection)
        {
            string formatString = "SELECT R.* INTO {0}  FROM vSrchObjectPropertyStorage R WHERE " + Environment.NewLine;
            string itemTemplate=string .Empty ;
            string[] itemParam = { string.Empty, string.Empty };
            string itemValue = string.Empty;
            int j=-1;
            // Запрос не параметризован
            if(!selectCommand.ImStronglyParametrized ) 
                {
                    itemTemplate = "R.TagName='{0}' ";
                    HashSet<string> tagList = GetTagList();
                   // сформировать where 
                    int z=0;
                    foreach (string item in tagList)
                    {                        
                        itemValue = itemValue + string.Format(itemTemplate, item) + (z == tagList.Count-1 ?  string.Empty:" OR " );
                        z++;
                    }   

                }
            else // Запрос  параметризирован
            {
                itemValue = GetParamsWhere("R");
                
            }
            // если указана коллекция линкованых объектов - создать ограничение по их свойствам
            // AND (NOT R.PropertyName='ContactList' OR 
                j=linkCollection .Count ;
            if(j>0)
            {
                string linkItemTemplate ="NOT R.PropertyName='{0}' ";
                string linkItemValue = string.Empty;
                for(int i=0;i<j;  i++)
                {
                    itemParam[0]= linkCollection [i].LinkField ;
                    linkItemValue = linkItemValue + string.Format(linkItemTemplate, itemParam) + (i == j - 1 ? string.Empty : " OR "); 

                }
                itemValue = "("+itemValue+")" + " AND ( " +linkItemValue+")";
            }

            return string.Format(formatString +"IsObject=0 AND  ("+ itemValue+")", SqlScriptEngine.ScriptConst_SerachAreaTable);            

        }

        private string GetParamsWhere (string tablePref)
        {
            string itemTemplate=string .Empty ;
            string[] itemParam = { string.Empty, string.Empty, tablePref };
            string itemValue = string.Empty;
                int j=  selectCommand.Params .Count;
                for (int i = 0; i < j; i++)
                {
                    Word item = (Word)selectCommand.Params[i];
                    itemTemplate = GetParamWhere(tablePref, item);                 
                    itemValue = itemValue + string.Format(itemTemplate, itemParam) + (i == j - 1 ? string.Empty : " OR "); ;

                    
                }
                return itemValue;
        }
        
        private string GetParamWhere(string tablePref,Word item)
        {
            string itemTemplate = string.Empty;
            string[] itemParam = { string.Empty, string.Empty, tablePref };

            if (
                item.PropertyType != Grishko.SerilizeTools.SrchItemInfo.PropertyControlType.Tree &
                item.PropertyType != Grishko.SerilizeTools.SrchItemInfo.PropertyControlType.OnlyValueSrch &
                !item.ImFuncton)
            {
                itemTemplate = "({2}.TagName='{0}' AND ({1}))";
                itemParam[1] = item.GetSql(false,tablePref );
            }
            else
            {
                itemTemplate = "({2}.TagName='{0}')";

            }
            itemParam[0] = item.ParentTagName.TagName;
            return string.Format(itemTemplate, itemParam);
        }


        private HashSet<string> GetTagList()
        {
            List<Word> fullWordList = SrchInfo.GetSentenceDictionary(selectCommand.SearchAreaTemplateType);
            int j = fullWordList.Count;

            //сформировать список тегов
            HashSet<string> tagList = new HashSet<string>();
            for (int i = 0; i < j; i++)
            {
                if (null == fullWordList[i].ParentTagName)
                    continue;

                if (!tagList.Contains(fullWordList[i].ParentTagName.TagName))
                    tagList.Add(fullWordList[i].ParentTagName.TagName);
            }
            return tagList;

        }


        


    }
}
